﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using DataHelper;

namespace DAL
{
    public class DutyDao
    {
        #region 职位
        //自动关闭没有使用的职位
        public bool CloseNoUseDuty()
        {
            string strSQL = "Update Admin_Emp_Duty Set Statu='N' Where Statu='Y' and  ID not in (Select DutyID from Admin_Employee Where StateID<>3)";
            return SQLHelper.ExecuteSql(strSQL);
        }
        //关闭一个或一组
        public bool CloseDuty(string dutyID)
        {
            string strSQL = "Update Admin_Emp_Duty Set Statu='N' Where Statu='Y' and  ID in (" + dutyID+")";
            return SQLHelper.ExecuteSql(strSQL);
        }
        //开启一个或一组
        public bool OpenDuty(string dutyID)
        {
            string strSQL = "Update Admin_Emp_Duty Set Statu='Y' Where Statu='N' and  ID in (" + dutyID+")";
            return SQLHelper.ExecuteSql(strSQL);
        }
        //设置职位级别
        public bool SetDutyType(string dutyType, string filter)
        {
            string strSQL = "Update Admin_Emp_Duty Set TypeID=" + dutyType + " Where ID in " + filter;
            return SQLHelper.ExecuteSql(strSQL);
        }
        //定义职位用  获取职位列表
        public DataTable GetLists(string serWord, string statu, string sortWay)
        {
            //[ID], [Name], [AbsIndex], [OfDeptName], [Memo]
            string orderBy = "";
            if (sortWay == "Def") orderBy = " ORDER BY a.id ASC";
            else if (sortWay == "List") orderBy = " ORDER BY a.absindex ASC,a.OfDeptName ASC, a.TypeID ASC";
            else if (sortWay == "Sal") orderBy = " ORDER BY a.salindex ASC,a.OfDeptName ASC, a.TypeID ASC";

            StringBuilder strSQL = new StringBuilder();
            
            if (string.IsNullOrWhiteSpace(statu))
            {
                if (string.IsNullOrWhiteSpace(serWord))
                    strSQL.Append("SELECT a.*,b.Name as TypeName FROM Admin_Emp_Duty a,Admin_Emp_DutyType b where a.TypeID=b.ID  ");
                else
                    strSQL.Append("SELECT a.*,b.Name as TypeName FROM Admin_Emp_Duty  a,Admin_Emp_DutyType b where a.TypeID=b.ID and a.Name like '%" + serWord + "%'");

            }
            else
            {
                if (string.IsNullOrWhiteSpace(serWord))
                    strSQL.Append("SELECT a.*,b.Name as TypeName FROM Admin_Emp_Duty a,Admin_Emp_DutyType b where a.TypeID=b.ID and  (a.Statu ='" + statu + "') ");
                else
                    strSQL.Append( "SELECT a.*,b.Name as TypeName FROM Admin_Emp_Duty a,Admin_Emp_DutyType b where a.TypeID=b.ID and a.Name like '%" + serWord + "%'  and (a.Statu ='" + statu + "') ");
            }
            strSQL.Append(orderBy);
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());

        }
        //用在职位组
        public DataTable GetListsByArray(string idArray)
        {
            return SQLHelper.ExecuteSqlGetDataSet("select ID,[Name] as DutyName from Admin_Emp_Duty where id in " + idArray).Tables[0];
        }
        //根据部门ID获取部门使用的所有职位，用在设置职位下限
        public DataTable GetDutyByDeptID(string deptID)
        {
            string strSQL = "select ID,[Name] as DutyName from Admin_Emp_Duty where id in (select DutyID from Admin_Employee where StateID<>3 and DeptID=" + deptID + ")";
            return SQLHelper.ExecuteSqlGetDataSet(strSQL).Tables[0];
        
        }
        #endregion
        #region 职位组操作
        public bool DelDutyGroup(string empCode)
        {
            return SQLHelper.ExecuteSql("delete from Admin_Emp_DutyGroup where EmpCode='" + empCode + "'");
        }

        public DataTable GetGroupList()
        {
            DataSet ds=SQLHelper.ExecuteSqlGetDataSet("select a.*,b.EmpName,b.DutyName,b.DeptName from Admin_Emp_DutyGroup a,View_Emp b where a.EmpCode=b.EmpCode");
            if (ds == null)
                return null;
            else
              return ds.Tables[0];
        }
        //emp 获取职位组
        public DataTable GetGroupList(string account)
        {
            //select DutyArray from Admin_Emp_DutyGroup where EmpCode='" + account + "'"
            DataSet ds=SQLHelper.ExecuteSqlGetDataSet("select DutyArray from Admin_Emp_DutyGroup where EmpCode='" + account + "'");
            if (ds == null)
                return null;
            else
                return ds.Tables[0];
        }
        #endregion

        #region 设定职位人数下限使用
        //
        public bool SetDutyLowLimit(SqlParameter[] coll)
        {

            return SQLHelper.ExecutePorcedure("spJimBatchSetDutyLowLimit", coll);
        }
        /// <summary>
        /// 设定职位人数下限使用
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetDutyLists(string tableName, string name)
        {

            string strSQL = "select [ID],[Name] from " + tableName + " Where Statu='Y' and (OfDeptName like '%" + name + "%' or OfDeptName='共用' or OfDeptName is null) order by absindex ASC,[OfDeptName] ASC, [ID] ASC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);

        }
        //获取职位底限 的列表
        public DataTable GetDutyLowLimit(string deptID, string dutyName)
        {
            string sql = "select * from Admin_Emp_LowLimit where 1=1 ";
            if (deptID != "0")
                sql += " and DeptID = " + deptID;

            if (string.IsNullOrWhiteSpace(dutyName))
                sql += " and  DutyName like '%" + dutyName + "%' ";

            sql += "  order by DeptID asc,DutyName asc";

            return SQLHelper.ExecuteSqlGetDrToDt(sql);

        }
        //删除某个部门下的所有定义 删除人员下限
        public bool DelDutyLowLimit(string deptID)
        {
            string strSQL = "delete from  Admin_Emp_LowLimit where DeptID=" + deptID;
            return SQLHelper.ExecuteSql(strSQL);
        }
        #endregion

        //获取 ddlDutyType(不能随便修改) 职位级别中的顺序 
        public string getDutyTypeAbsIndex(string account)
        {
            string strSQL = "select c.AbsIndex from JimView_Account a,Admin_Emp_Duty b,Admin_Emp_DutyType c where a.DutyID=b.ID and b.TypeID=c.ID and a.account='"+account+"'";
            return SQLHelper.ExecuteSqlGetNumber(strSQL);
        }
    }
}
